/**
 * Excel导出工具
 */

import * as XLSX from 'xlsx'
import type { Event, FoodIntake, Food } from '@/types'
import { generateNutritionReport } from './nutrition'
import { getNutrientFieldGroups, nutrientCategories, categoryNutrientRelations, nutrients } from '@/data/nutrientCategories'

/**
 * 饮食跟踪记录的Excel导出数据结构
 */
interface DietTrackingExportData {
  事件创建日期: string
  事件创建时间: string
  事件日期: string
  事件时间: string
  食物名称: string
  摄入量_克: number
  能量_千卡: number
  蛋白质_克: number
  脂肪_克: number
  碳水化合物_克: number
  膳食纤维_克: number
  胆固醇_毫克: number
  钠_毫克: number
  钙_毫克: number
  磷_毫克: number
  钾_毫克: number
  铁_毫克: number
  维生素A_微克: number
  维生素C_毫克: number
  维生素D_微克: number
  维生素E_毫克: number
  硫胺素B1_毫克: number
  核黄素B2_毫克: number
  维生素B6_毫克: number
  维生素B12_微克: number
  烟酸_毫克: number
  叶酸_微克: number
  其他营养素: string
  备注: string
}

/**
 * 营养汇总的Excel导出数据结构
 */
interface NutritionSummaryExportData {
  事件创建日期: string
  事件创建时间: string
  事件日期: string
  总食物数量: number
  总重量_克: number
  总能量_千卡: number
  总蛋白质_克: number
  总脂肪_克: number
  总碳水化合物_克: number
  总膳食纤维_克: number
  总胆固醇_毫克: number
  总钠_毫克: number
  总钙_毫克: number
  总磷_毫克: number
  总钾_毫克: number
  总铁_毫克: number
  总维生素A_微克: number
  总维生素C_毫克: number
  总维生素D_微克: number
  总维生素E_毫克: number
  总硫胺素B1_毫克: number
  总核黄素B2_毫克: number
  总维生素B6_毫克: number
  总维生素B12_微克: number
  总烟酸_毫克: number
  总叶酸_微克: number
  其他营养素汇总: string
  备注: string
}

/**
 * 营养素分类统计的Excel导出数据结构
 */
interface NutrientCategoryStatsExportData {
  日期: string
  营养素大分类: string
  分类描述: string
  总摄入量: number
  单位说明: string
}

/**
 * 将饮食跟踪事件转换为Excel导出数据
 */
export function convertDietEventsToExportData(events: Event[]): {
  detailData: DietTrackingExportData[]
  summaryData: NutritionSummaryExportData[]
} {
  const detailData: DietTrackingExportData[] = []
  const summaryData: NutritionSummaryExportData[] = []

  events.forEach(event => {
    // 检查是否有饮食摄入数据
    let foods_intake: FoodIntake[] = []

    // 处理不同的数据结构
    if (event.extra_data?.foods_intake) {
      if (Array.isArray(event.extra_data.foods_intake)) {
        foods_intake = event.extra_data.foods_intake
      } else if (event.extra_data.foods_intake.foods_intake && Array.isArray(event.extra_data.foods_intake.foods_intake)) {
        foods_intake = event.extra_data.foods_intake.foods_intake
      }
    }

    if (foods_intake.length === 0) return

    // 获取事件创建时间和事件时间
    const createdAt = new Date(event.created_at || new Date())
    const eventCreatedDate = createdAt.toLocaleDateString('zh-CN')
    const eventCreatedTime = createdAt.toLocaleTimeString('zh-CN', {
      hour12: false,
      hour: '2-digit',
      minute: '2-digit'
    })

    const eventTime = event.start_time || new Date().toLocaleTimeString('zh-CN', {
      hour12: false,
      hour: '2-digit',
      minute: '2-digit'
    })

    // 详细记录
    foods_intake.forEach((intake: FoodIntake) => {
      // 处理other_nutrients
      let otherNutrientsStr = ''
      if (intake.food.other_nutrients && intake.food.other_nutrients.length > 0) {
        const otherNutrientsList = intake.food.other_nutrients.map(n => {
          const amount = (n.amount || 0) * intake.amount_g / 100
          return `${n.name_zh}:${amount.toFixed(2)}${n.unit}`
        })
        otherNutrientsStr = otherNutrientsList.join('; ')
      }

      detailData.push({
        事件创建日期: eventCreatedDate,
        事件创建时间: eventCreatedTime,
        事件日期: event.start_date,
        事件时间: eventTime,
        食物名称: intake.food.name_zh || '',
        摄入量_克: intake.amount_g,
        能量_千卡: intake.calculated_nutrition?.energy_kcal || 0,
        蛋白质_克: intake.calculated_nutrition?.protein_g || 0,
        脂肪_克: intake.calculated_nutrition?.fat_g || 0,
        碳水化合物_克: intake.calculated_nutrition?.carb_g || 0,
        膳食纤维_克: intake.calculated_nutrition?.fiber_g || 0,
        胆固醇_毫克: (intake.food.cholesterol_mg || 0) * intake.amount_g / 100,
        钠_毫克: intake.calculated_nutrition?.sodium_mg || 0,
        钙_毫克: intake.calculated_nutrition?.calcium_mg || 0,
        磷_毫克: (intake.food.phosphorus_mg || 0) * intake.amount_g / 100,
        钾_毫克: (intake.food.potassium_mg || 0) * intake.amount_g / 100,
        铁_毫克: intake.calculated_nutrition?.iron_mg || 0,
        维生素A_微克: (intake.food.vitamin_a_ug || 0) * intake.amount_g / 100,
        维生素C_毫克: intake.calculated_nutrition?.vitamin_c_mg || 0,
        维生素D_微克: (intake.food.vitamin_d_mcg || 0) * intake.amount_g / 100,
        维生素E_毫克: (intake.food.vitamin_e_mg || 0) * intake.amount_g / 100,
        硫胺素B1_毫克: (intake.food.thiamin_mg || 0) * intake.amount_g / 100,
        核黄素B2_毫克: (intake.food.riboflavin_mg || 0) * intake.amount_g / 100,
        维生素B6_毫克: (intake.food.vitamin_b6_mg || 0) * intake.amount_g / 100,
        维生素B12_微克: (intake.food.vitamin_b12_mcg || 0) * intake.amount_g / 100,
        烟酸_毫克: (intake.food.niacin_mg || 0) * intake.amount_g / 100,
        叶酸_微克: (intake.food.folate_mcg || 0) * intake.amount_g / 100,
        其他营养素: otherNutrientsStr,
        备注: event.description || ''
      })
    })

    // 汇总记录
    const report = generateNutritionReport(foods_intake, event.start_date)

    // 计算额外的营养成分总和
    let totalCholesterol = 0
    let totalPhosphorus = 0
    let totalPotassium = 0
    let totalVitaminA = 0
    let totalVitaminD = 0
    let totalVitaminE = 0
    let totalThiamin = 0
    let totalRiboflavin = 0
    let totalVitaminB6 = 0
    let totalVitaminB12 = 0
    let totalNiacin = 0
    let totalFolate = 0

    // 汇总other_nutrients
    const otherNutrientsMap = new Map<number, { name_zh: string; name_en?: string; amount: number; unit: string; rich: boolean }>()

    foods_intake.forEach((intake: FoodIntake) => {
      const ratio = intake.amount_g / 100
      totalCholesterol += (intake.food.cholesterol_mg || 0) * ratio
      totalPhosphorus += (intake.food.phosphorus_mg || 0) * ratio
      totalPotassium += (intake.food.potassium_mg || 0) * ratio
      totalVitaminA += (intake.food.vitamin_a_ug || 0) * ratio
      totalVitaminD += (intake.food.vitamin_d_mcg || 0) * ratio
      totalVitaminE += (intake.food.vitamin_e_mg || 0) * ratio
      totalThiamin += (intake.food.thiamin_mg || 0) * ratio
      totalRiboflavin += (intake.food.riboflavin_mg || 0) * ratio
      totalVitaminB6 += (intake.food.vitamin_b6_mg || 0) * ratio
      totalVitaminB12 += (intake.food.vitamin_b12_mcg || 0) * ratio
      totalNiacin += (intake.food.niacin_mg || 0) * ratio
      totalFolate += (intake.food.folate_mcg || 0) * ratio

      // 汇总other_nutrients
      if (intake.food.other_nutrients && intake.food.other_nutrients.length > 0) {
        intake.food.other_nutrients.forEach(nutrient => {
          const nutrientId = nutrient.nutrient_id || 0
          const scaledAmount = (nutrient.amount || 0) * ratio

          if (otherNutrientsMap.has(nutrientId)) {
            const existing = otherNutrientsMap.get(nutrientId)!
            existing.amount += scaledAmount
            existing.rich = existing.rich || nutrient.rich
          } else {
            otherNutrientsMap.set(nutrientId, {
              name_zh: nutrient.name_zh,
              name_en: nutrient.name_en,
              amount: scaledAmount,
              unit: nutrient.unit,
              rich: nutrient.rich
            })
          }
        })
      }
    })

    // 格式化other_nutrients汇总
    let otherNutrientsStr = ''
    if (otherNutrientsMap.size > 0) {
      const otherNutrientsList = Array.from(otherNutrientsMap.values()).map(n =>
        `${n.name_zh}:${n.amount.toFixed(2)}${n.unit}`
      )
      otherNutrientsStr = otherNutrientsList.join('; ')
    }

    summaryData.push({
      事件创建日期: eventCreatedDate,
      事件创建时间: eventCreatedTime,
      事件日期: event.start_date,
      总食物数量: report.totalFoods,
      总重量_克: report.totalWeight,
      总能量_千卡: report.nutrition.energy_kcal,
      总蛋白质_克: report.nutrition.protein_g,
      总脂肪_克: report.nutrition.fat_g,
      总碳水化合物_克: report.nutrition.carb_g,
      总膳食纤维_克: report.nutrition.fiber_g,
      总胆固醇_毫克: totalCholesterol,
      总钠_毫克: report.nutrition.sodium_mg,
      总钙_毫克: report.nutrition.calcium_mg,
      总磷_毫克: totalPhosphorus,
      总钾_毫克: totalPotassium,
      总铁_毫克: report.nutrition.iron_mg,
      总维生素A_微克: totalVitaminA,
      总维生素C_毫克: report.nutrition.vitamin_c_mg,
      总维生素D_微克: totalVitaminD,
      总维生素E_毫克: totalVitaminE,
      总硫胺素B1_毫克: totalThiamin,
      总核黄素B2_毫克: totalRiboflavin,
      总维生素B6_毫克: totalVitaminB6,
      总维生素B12_微克: totalVitaminB12,
      总烟酸_毫克: totalNiacin,
      总叶酸_微克: totalFolate,
      其他营养素汇总: otherNutrientsStr,
      备注: event.description || ''
    })
  })

  return { detailData, summaryData }
}

/**
 * 生成营养素分类统计数据
 * 按日期和大分类统计营养素摄入总量
 */
export function generateNutrientCategoryStats(events: Event[]): NutrientCategoryStatsExportData[] {
  const statsData: NutrientCategoryStatsExportData[] = []
  const nutrientGroups = getNutrientFieldGroups()

  // 按日期和分类统计总量
  // 结构: { 日期: { 分类ID: 总量 } }
  const dailyStats: Record<string, Record<number, number>> = {}

  events.forEach(event => {
    // 检查是否有饮食摄入数据
    let foods_intake: FoodIntake[] = []

    if (event.extra_data?.foods_intake) {
      if (Array.isArray(event.extra_data.foods_intake)) {
        foods_intake = event.extra_data.foods_intake
      } else if (event.extra_data.foods_intake.foods_intake && Array.isArray(event.extra_data.foods_intake.foods_intake)) {
        foods_intake = event.extra_data.foods_intake.foods_intake
      }
    }

    if (foods_intake.length === 0) return

    const date = event.start_date
    if (!dailyStats[date]) {
      dailyStats[date] = {}
    }

    // 遍历每个食物摄入
    foods_intake.forEach(intake => {
      const food = intake.food
      const ratio = intake.amount_g / 100 // 计算比例

      // 遍历每个营养素分组，计算该分类的总摄入量
      nutrientGroups.forEach(group => {
        const categoryId = group.majorCategory.category_id

        if (!dailyStats[date][categoryId]) {
          dailyStats[date][categoryId] = 0
        }

        // 累加该分组中所有营养素的值
        group.fields.forEach(field => {
          const value = food[field.key as keyof Food]
          if (value !== undefined && typeof value === 'number') {
            const calculatedValue = value * ratio
            dailyStats[date][categoryId] += calculatedValue
          }
        })
      })
    })
  })

  // 转换为导出数据格式
  Object.keys(dailyStats).sort().forEach(date => {
    nutrientGroups.forEach(group => {
      const categoryId = group.majorCategory.category_id
      const totalValue = dailyStats[date][categoryId]

      if (totalValue !== undefined && totalValue > 0) {
        const row: NutrientCategoryStatsExportData = {
          日期: date,
          营养素大分类: group.majorCategory.category_name,
          分类描述: group.majorCategory.category_description,
          总摄入量: Math.round(totalValue * 100) / 100,
          单位说明: '各营养素数值总和（不同单位）'
        }

        statsData.push(row)
      }
    })
  })

  return statsData
}

/**
 * 生成营养素分类统计数据（按分类level统计）
 * @param events 事件列表
 * @param level 分类层级：'major' | 'category' | 'subcategory'
 */
export function generateNutrientCategoryStatsByLevel(
  events: Event[],
  level: 'major' | 'category' | 'subcategory'
): NutrientCategoryStatsExportData[] {
  const statsData: NutrientCategoryStatsExportData[] = []

  // 获取指定层级的所有分类
  const categories = nutrientCategories.filter(cat => cat.category_level === level)

  // 创建营养素ID到字段名的映射
  const nutrientIdToFieldMap: Record<number, string> = {}
  const nutrientGroups = getNutrientFieldGroups()
  nutrientGroups.forEach(group => {
    group.fields.forEach(field => {
      nutrientIdToFieldMap[field.nutrientId] = field.key
    })
  })

  // 按日期和分类统计总量
  // 结构: { 日期: { 分类ID: 总量 } }
  const dailyStats: Record<string, Record<number, number>> = {}

  events.forEach(event => {
    // 检查是否有饮食摄入数据
    let foods_intake: FoodIntake[] = []

    if (event.extra_data?.foods_intake) {
      if (Array.isArray(event.extra_data.foods_intake)) {
        foods_intake = event.extra_data.foods_intake
      } else if (event.extra_data.foods_intake.foods_intake && Array.isArray(event.extra_data.foods_intake.foods_intake)) {
        foods_intake = event.extra_data.foods_intake.foods_intake
      }
    }

    if (foods_intake.length === 0) return

    const date = event.start_date
    if (!dailyStats[date]) {
      dailyStats[date] = {}
    }

    // 遍历每个食物摄入
    foods_intake.forEach(intake => {
      const food = intake.food
      const ratio = intake.amount_g / 100 // 计算比例

      // 遍历每个分类
      categories.forEach(category => {
        const categoryId = category.category_id

        if (!dailyStats[date][categoryId]) {
          dailyStats[date][categoryId] = 0
        }

        // 获取该分类下的所有营养素
        const nutrientIds = categoryNutrientRelations
          .filter(rel => rel.category_id === categoryId)
          .map(rel => rel.nutrient_id)

        // 累加该分类下所有营养素的值
        nutrientIds.forEach(nutrientId => {
          const fieldKey = nutrientIdToFieldMap[nutrientId]
          if (fieldKey) {
            const value = food[fieldKey as keyof Food]
            if (value !== undefined && typeof value === 'number') {
              const calculatedValue = value * ratio
              dailyStats[date][categoryId] += calculatedValue
            }
          }
        })
      })
    })
  })

  // 转换为导出数据格式
  Object.keys(dailyStats).sort().forEach(date => {
    categories.forEach(category => {
      const categoryId = category.category_id
      const totalValue = dailyStats[date][categoryId]

      if (totalValue !== undefined && totalValue > 0) {
        // 获取父分类名称（如果有）
        let fullCategoryName = category.category_name
        if (category.parent_category_id) {
          const parentCategory = nutrientCategories.find(c => c.category_id === category.parent_category_id)
          if (parentCategory) {
            fullCategoryName = `${parentCategory.category_name} > ${category.category_name}`
          }
        }

        const row: NutrientCategoryStatsExportData = {
          日期: date,
          营养素大分类: fullCategoryName,
          分类描述: category.category_description,
          总摄入量: Math.round(totalValue * 100) / 100,
          单位说明: '各营养素数值总和（不同单位）'
        }

        statsData.push(row)
      }
    })
  })

  return statsData
}

/**
 * 营养素详细统计数据结构（方案1）
 */
interface NutrientDetailStatsData {
  日期: string
  营养素分类: string
  营养素名称: string
  摄入量: number
  单位: string
  推荐量: string
  充足度: string
  状态: string
}

/**
 * 营养素充足度评分数据结构（方案2）
 */
interface NutrientAdequacyScoreData {
  日期: string
  营养素分类: string
  平均充足度: string
  不足营养素: string
  过量营养素: string
}

/**
 * 宏量营养素能量分析数据结构（方案3）
 */
interface MacroNutrientEnergyData {
  日期: string
  总能量_kcal: number
  碳水化合物_g: number
  碳水化合物_kcal: number
  碳水化合物_占比: string
  蛋白质_g: number
  蛋白质_kcal: number
  蛋白质_占比: string
  脂肪_g: number
  脂肪_kcal: number
  脂肪_占比: string
  碳水状态: string
  蛋白质状态: string
  脂肪状态: string
}

/**
 * 生成营养素详细统计（方案1）
 */
export function generateNutrientDetailStats(events: Event[]): NutrientDetailStatsData[] {
  const statsData: NutrientDetailStatsData[] = []
  const nutrientGroups = getNutrientFieldGroups()

  // 按日期统计
  const dailyNutrients: Record<string, Record<string, number>> = {}

  events.forEach(event => {
    let foods_intake: FoodIntake[] = []

    if (event.extra_data?.foods_intake) {
      if (Array.isArray(event.extra_data.foods_intake)) {
        foods_intake = event.extra_data.foods_intake
      } else if (event.extra_data.foods_intake.foods_intake && Array.isArray(event.extra_data.foods_intake.foods_intake)) {
        foods_intake = event.extra_data.foods_intake.foods_intake
      }
    }

    if (foods_intake.length === 0) return

    const date = event.start_date
    if (!dailyNutrients[date]) {
      dailyNutrients[date] = {}
    }

    foods_intake.forEach(intake => {
      const food = intake.food
      const ratio = intake.amount_g / 100

      nutrientGroups.forEach(group => {
        group.fields.forEach(field => {
          const value = food[field.key as keyof Food]
          if (value !== undefined && typeof value === 'number') {
            if (!dailyNutrients[date][field.key]) {
              dailyNutrients[date][field.key] = 0
            }
            dailyNutrients[date][field.key] += value * ratio
          }
        })
      })
    })
  })

  // 转换为导出格式
  Object.keys(dailyNutrients).sort().forEach(date => {
    nutrientGroups.forEach(group => {
      group.fields.forEach(field => {
        const intake = dailyNutrients[date][field.key] || 0
        const nutrient = nutrients.find(n => n.id === field.nutrientId)

        if (!nutrient) return

        // 计算充足度
        let adequacy = ''
        let status = '未知'

        if (nutrient.china_recommended_amount) {
          // 解析推荐量
          const recStr = nutrient.china_recommended_amount
          const rangeMatch = recStr.match(/(\d+(?:\.\d+)?)-(\d+(?:\.\d+)?)/)
          const lessThanMatch = recStr.match(/<(\d+(?:\.\d+)?)/)
          const singleMatch = recStr.match(/(\d+(?:\.\d+)?)/)

          let referenceValue: number | null = null

          if (rangeMatch) {
            const min = Number(rangeMatch[1])
            const max = Number(rangeMatch[2])
            referenceValue = (min + max) / 2

            if (intake < min * 0.8) {
              status = '⚠️ 不足'
            } else if (intake > max * 1.2) {
              status = '⚠️ 过量'
            } else {
              status = '✓ 达标'
            }
          } else if (lessThanMatch) {
            referenceValue = Number(lessThanMatch[1])

            if (intake > referenceValue * 1.2) {
              status = '⚠️ 过量'
            } else {
              status = '✓ 达标'
            }
          } else if (singleMatch) {
            referenceValue = Number(singleMatch[1])

            if (intake < referenceValue * 0.8) {
              status = '⚠️ 不足'
            } else if (intake > referenceValue * 1.2) {
              status = '⚠️ 过量'
            } else {
              status = '✓ 达标'
            }
          }

          if (referenceValue) {
            adequacy = `${Math.round((intake / referenceValue) * 100)}%`
          }
        }

        statsData.push({
          日期: date,
          营养素分类: group.majorCategory.category_name,
          营养素名称: field.label,
          摄入量: Math.round(intake * 100) / 100,
          单位: field.unit,
          推荐量: nutrient.china_recommended_amount || '无',
          充足度: adequacy || '-',
          状态: status
        })
      })
    })
  })

  return statsData
}

/**
 * 生成营养素充足度评分（方案2）
 */
export function generateNutrientAdequacyScore(events: Event[]): NutrientAdequacyScoreData[] {
  const statsData: NutrientAdequacyScoreData[] = []
  const nutrientGroups = getNutrientFieldGroups()

  // 按日期和分类统计
  const dailyStats: Record<string, Record<number, { total: number; count: number; insufficient: string[]; excessive: string[] }>> = {}

  events.forEach(event => {
    let foods_intake: FoodIntake[] = []

    if (event.extra_data?.foods_intake) {
      if (Array.isArray(event.extra_data.foods_intake)) {
        foods_intake = event.extra_data.foods_intake
      } else if (event.extra_data.foods_intake.foods_intake && Array.isArray(event.extra_data.foods_intake.foods_intake)) {
        foods_intake = event.extra_data.foods_intake.foods_intake
      }
    }

    if (foods_intake.length === 0) return

    const date = event.start_date
    if (!dailyStats[date]) {
      dailyStats[date] = {}
    }

    foods_intake.forEach(intake => {
      const food = intake.food
      const ratio = intake.amount_g / 100

      nutrientGroups.forEach(group => {
        const categoryId = group.majorCategory.category_id

        if (!dailyStats[date][categoryId]) {
          dailyStats[date][categoryId] = { total: 0, count: 0, insufficient: [], excessive: [] }
        }

        group.fields.forEach(field => {
          const value = food[field.key as keyof Food]
          if (value !== undefined && typeof value === 'number') {
            const intakeValue = value * ratio
            const nutrient = nutrients.find(n => n.id === field.nutrientId)

            if (nutrient && nutrient.china_recommended_amount) {
              // 计算充足度
              const recStr = nutrient.china_recommended_amount
              const rangeMatch = recStr.match(/(\d+(?:\.\d+)?)-(\d+(?:\.\d+)?)/)
              const lessThanMatch = recStr.match(/<(\d+(?:\.\d+)?)/)
              const singleMatch = recStr.match(/(\d+(?:\.\d+)?)/)

              let referenceValue: number | null = null

              if (rangeMatch) {
                const min = Number(rangeMatch[1])
                const max = Number(rangeMatch[2])
                referenceValue = (min + max) / 2
              } else if (lessThanMatch) {
                referenceValue = Number(lessThanMatch[1])
              } else if (singleMatch) {
                referenceValue = Number(singleMatch[1])
              }

              if (referenceValue) {
                const adequacy = (intakeValue / referenceValue) * 100
                dailyStats[date][categoryId].total += adequacy
                dailyStats[date][categoryId].count++

                if (adequacy < 80) {
                  dailyStats[date][categoryId].insufficient.push(`${field.label}(${Math.round(adequacy)}%)`)
                } else if (adequacy > 120) {
                  dailyStats[date][categoryId].excessive.push(`${field.label}(${Math.round(adequacy)}%)`)
                }
              }
            }
          }
        })
      })
    })
  })

  // 转换为导出格式
  Object.keys(dailyStats).sort().forEach(date => {
    nutrientGroups.forEach(group => {
      const categoryId = group.majorCategory.category_id
      const stats = dailyStats[date][categoryId]

      if (stats && stats.count > 0) {
        const avgAdequacy = stats.total / stats.count

        statsData.push({
          日期: date,
          营养素分类: group.majorCategory.category_name,
          平均充足度: `${Math.round(avgAdequacy)}%`,
          不足营养素: stats.insufficient.join('; ') || '-',
          过量营养素: stats.excessive.join('; ') || '-'
        })
      }
    })
  })

  return statsData
}

/**
 * 生成宏量营养素能量分析（方案3）
 */
export function generateMacroNutrientEnergyAnalysis(events: Event[]): MacroNutrientEnergyData[] {
  const statsData: MacroNutrientEnergyData[] = []

  // 按日期统计
  const dailyMacros: Record<string, { carb: number; protein: number; fat: number }> = {}

  events.forEach(event => {
    let foods_intake: FoodIntake[] = []

    if (event.extra_data?.foods_intake) {
      if (Array.isArray(event.extra_data.foods_intake)) {
        foods_intake = event.extra_data.foods_intake
      } else if (event.extra_data.foods_intake.foods_intake && Array.isArray(event.extra_data.foods_intake.foods_intake)) {
        foods_intake = event.extra_data.foods_intake.foods_intake
      }
    }

    if (foods_intake.length === 0) return

    const date = event.start_date
    if (!dailyMacros[date]) {
      dailyMacros[date] = { carb: 0, protein: 0, fat: 0 }
    }

    foods_intake.forEach(intake => {
      const food = intake.food
      const ratio = intake.amount_g / 100

      dailyMacros[date].carb += (food.carb_g || 0) * ratio
      dailyMacros[date].protein += (food.protein_g || 0) * ratio
      dailyMacros[date].fat += (food.fat_g || 0) * ratio
    })
  })

  // 转换为导出格式
  Object.keys(dailyMacros).sort().forEach(date => {
    const macros = dailyMacros[date]

    const carbKcal = macros.carb * 4
    const proteinKcal = macros.protein * 4
    const fatKcal = macros.fat * 9
    const totalKcal = carbKcal + proteinKcal + fatKcal

    const carbPercentage = totalKcal > 0 ? (carbKcal / totalKcal) * 100 : 0
    const proteinPercentage = totalKcal > 0 ? (proteinKcal / totalKcal) * 100 : 0
    const fatPercentage = totalKcal > 0 ? (fatKcal / totalKcal) * 100 : 0

    // 评估状态
    const carbStatus = carbPercentage >= 50 && carbPercentage <= 65 ? '✓ 达标' :
                       carbPercentage < 50 * 0.8 ? '⚠️ 偏低' : '⚠️ 偏高'
    const proteinStatus = proteinPercentage >= 10 && proteinPercentage <= 20 ? '✓ 达标' :
                          proteinPercentage < 10 * 0.8 ? '⚠️ 偏低' : '⚠️ 偏高'
    const fatStatus = fatPercentage >= 20 && fatPercentage <= 30 ? '✓ 达标' :
                      fatPercentage < 20 * 0.8 ? '⚠️ 偏低' : '⚠️ 偏高'

    statsData.push({
      日期: date,
      总能量_kcal: Math.round(totalKcal),
      碳水化合物_g: Math.round(macros.carb * 10) / 10,
      碳水化合物_kcal: Math.round(carbKcal),
      碳水化合物_占比: `${Math.round(carbPercentage)}% (推荐50-65%)`,
      蛋白质_g: Math.round(macros.protein * 10) / 10,
      蛋白质_kcal: Math.round(proteinKcal),
      蛋白质_占比: `${Math.round(proteinPercentage)}% (推荐10-20%)`,
      脂肪_g: Math.round(macros.fat * 10) / 10,
      脂肪_kcal: Math.round(fatKcal),
      脂肪_占比: `${Math.round(fatPercentage)}% (推荐20-30%)`,
      碳水状态: carbStatus,
      蛋白质状态: proteinStatus,
      脂肪状态: fatStatus
    })
  })

  return statsData
}

/**
 * 导出饮食跟踪记录到Excel
 */
export function exportDietTrackingToExcel(
  events: Event[],
  options: {
    filename?: string
    includeDetail?: boolean
    includeSummary?: boolean
    dateRange?: { start: string; end: string }
  } = {}
) {
  const {
    filename = `饮食跟踪记录_${new Date().toISOString().split('T')[0]}.xlsx`,
    includeDetail = true,
    includeSummary = true,
    dateRange
  } = options

  // 过滤日期范围
  let filteredEvents = events
  if (dateRange) {
    filteredEvents = events.filter(event => 
      event.start_date >= dateRange.start && event.start_date <= dateRange.end
    )
  }

  const { detailData, summaryData } = convertDietEventsToExportData(filteredEvents)

  // 生成营养素分类统计数据（三个层级）
  const majorCategoryStatsData = generateNutrientCategoryStats(filteredEvents) // 大分类
  const categoryStatsData = generateNutrientCategoryStatsByLevel(filteredEvents, 'category') // 分类
  const subcategoryStatsData = generateNutrientCategoryStatsByLevel(filteredEvents, 'subcategory') // 子分类

  // 生成科学营养统计数据（A方案）
  const nutrientDetailStats = generateNutrientDetailStats(filteredEvents) // 方案1：营养素详细统计
  const adequacyScoreStats = generateNutrientAdequacyScore(filteredEvents) // 方案2：充足度评分
  const macroEnergyStats = generateMacroNutrientEnergyAnalysis(filteredEvents) // 方案3：宏量营养素能量分析

  // 创建工作簿
  const workbook = XLSX.utils.book_new()

  // 添加详细记录工作表
  if (includeDetail && detailData.length > 0) {
    const detailWorksheet = XLSX.utils.json_to_sheet(detailData)

    // 设置列宽
    const detailColWidths = [
      { wch: 12 }, // 日期
      { wch: 8 },  // 餐次
      { wch: 20 }, // 食物名称
      { wch: 10 }, // 摄入量
      { wch: 10 }, // 能量
      { wch: 10 }, // 蛋白质
      { wch: 10 }, // 脂肪
      { wch: 12 }, // 碳水化合物
      { wch: 10 }, // 膳食纤维
      { wch: 8 },  // 钠
      { wch: 8 },  // 钙
      { wch: 8 },  // 铁
      { wch: 10 }, // 维生素C
      { wch: 20 }  // 备注
    ]
    detailWorksheet['!cols'] = detailColWidths

    XLSX.utils.book_append_sheet(workbook, detailWorksheet, '详细记录')
  }

  // 添加汇总记录工作表
  if (includeSummary && summaryData.length > 0) {
    const summaryWorksheet = XLSX.utils.json_to_sheet(summaryData)

    // 设置列宽
    const summaryColWidths = [
      { wch: 12 }, // 日期
      { wch: 10 }, // 总食物数量
      { wch: 10 }, // 总重量
      { wch: 12 }, // 总能量
      { wch: 12 }, // 总蛋白质
      { wch: 10 }, // 总脂肪
      { wch: 14 }, // 总碳水化合物
      { wch: 12 }, // 总膳食纤维
      { wch: 10 }, // 总钠
      { wch: 10 }, // 总钙
      { wch: 10 }, // 总铁
      { wch: 12 }, // 总维生素C
      { wch: 20 }  // 备注
    ]
    summaryWorksheet['!cols'] = summaryColWidths

    XLSX.utils.book_append_sheet(workbook, summaryWorksheet, '营养汇总')
  }

  // 添加营养素大分类统计工作表
  if (majorCategoryStatsData.length > 0) {
    const majorCategoryStatsWorksheet = XLSX.utils.json_to_sheet(majorCategoryStatsData)

    // 设置列宽
    const statsColWidths = [
      { wch: 12 }, // 日期
      { wch: 20 }, // 营养素大分类
      { wch: 40 }, // 分类描述
      { wch: 12 }, // 总摄入量
      { wch: 30 }, // 单位说明
    ]
    majorCategoryStatsWorksheet['!cols'] = statsColWidths

    XLSX.utils.book_append_sheet(workbook, majorCategoryStatsWorksheet, '大分类统计')
  }

  // 添加营养素分类统计工作表
  if (categoryStatsData.length > 0) {
    const categoryStatsWorksheet = XLSX.utils.json_to_sheet(categoryStatsData)
    categoryStatsWorksheet['!cols'] = [
      { wch: 12 }, // 日期
      { wch: 30 }, // 营养素分类
      { wch: 40 }, // 分类描述
      { wch: 12 }, // 总摄入量
      { wch: 30 }, // 单位说明
    ]
    XLSX.utils.book_append_sheet(workbook, categoryStatsWorksheet, '分类统计')
  }

  // 添加营养素子分类统计工作表
  if (subcategoryStatsData.length > 0) {
    const subcategoryStatsWorksheet = XLSX.utils.json_to_sheet(subcategoryStatsData)
    subcategoryStatsWorksheet['!cols'] = [
      { wch: 12 }, // 日期
      { wch: 40 }, // 营养素子分类
      { wch: 40 }, // 分类描述
      { wch: 12 }, // 总摄入量
      { wch: 30 }, // 单位说明
    ]
    XLSX.utils.book_append_sheet(workbook, subcategoryStatsWorksheet, '子分类统计')
  }

  // 添加营养素详细统计工作表（方案1）
  if (nutrientDetailStats.length > 0) {
    const detailStatsWorksheet = XLSX.utils.json_to_sheet(nutrientDetailStats)
    detailStatsWorksheet['!cols'] = [
      { wch: 12 }, // 日期
      { wch: 15 }, // 营养素分类
      { wch: 15 }, // 营养素名称
      { wch: 10 }, // 摄入量
      { wch: 8 },  // 单位
      { wch: 20 }, // 推荐量
      { wch: 10 }, // 充足度
      { wch: 12 }, // 状态
    ]
    XLSX.utils.book_append_sheet(workbook, detailStatsWorksheet, '营养素详细统计')
  }

  // 添加营养素充足度评分工作表（方案2）
  if (adequacyScoreStats.length > 0) {
    const scoreStatsWorksheet = XLSX.utils.json_to_sheet(adequacyScoreStats)
    scoreStatsWorksheet['!cols'] = [
      { wch: 12 }, // 日期
      { wch: 15 }, // 营养素分类
      { wch: 12 }, // 平均充足度
      { wch: 40 }, // 不足营养素
      { wch: 40 }, // 过量营养素
    ]
    XLSX.utils.book_append_sheet(workbook, scoreStatsWorksheet, '营养素充足度评分')
  }

  // 添加宏量营养素能量分析工作表（方案3）
  if (macroEnergyStats.length > 0) {
    const macroStatsWorksheet = XLSX.utils.json_to_sheet(macroEnergyStats)
    macroStatsWorksheet['!cols'] = [
      { wch: 12 }, // 日期
      { wch: 12 }, // 总能量
      { wch: 12 }, // 碳水化合物_g
      { wch: 14 }, // 碳水化合物_kcal
      { wch: 25 }, // 碳水化合物_占比
      { wch: 10 }, // 蛋白质_g
      { wch: 12 }, // 蛋白质_kcal
      { wch: 25 }, // 蛋白质_占比
      { wch: 10 }, // 脂肪_g
      { wch: 12 }, // 脂肪_kcal
      { wch: 25 }, // 脂肪_占比
      { wch: 12 }, // 碳水状态
      { wch: 12 }, // 蛋白质状态
      { wch: 12 }, // 脂肪状态
    ]
    XLSX.utils.book_append_sheet(workbook, macroStatsWorksheet, '宏量营养素能量分析')
  }

  // 如果没有数据，创建一个空的工作表
  if (!includeDetail && !includeSummary || (detailData.length === 0 && summaryData.length === 0)) {
    const emptyWorksheet = XLSX.utils.json_to_sheet([{ 提示: '没有找到饮食跟踪记录' }])
    XLSX.utils.book_append_sheet(workbook, emptyWorksheet, '无数据')
  }

  // 导出文件
  XLSX.writeFile(workbook, filename)
}

/**
 * 导出营养分析报告到Excel
 */
export function exportNutritionAnalysisToExcel(
  events: Event[],
  options: {
    filename?: string
    dateRange?: { start: string; end: string }
  } = {}
) {
  const {
    filename = `营养分析报告_${new Date().toISOString().split('T')[0]}.xlsx`,
    dateRange
  } = options

  // 过滤日期范围
  let filteredEvents = events
  if (dateRange) {
    filteredEvents = events.filter(event => 
      event.start_date >= dateRange.start && event.start_date <= dateRange.end
    )
  }

  const workbook = XLSX.utils.book_new()

  // 按日期分组统计
  const dailyStats: Record<string, any> = {}
  
  filteredEvents.forEach(event => {
    if (!event.extra_data?.foods_intake) return

    const date = event.start_date
    if (!dailyStats[date]) {
      dailyStats[date] = {
        日期: date,
        餐次数: 0,
        食物种类数: 0,
        总重量_克: 0,
        总能量_千卡: 0,
        总蛋白质_克: 0,
        总脂肪_克: 0,
        总碳水化合物_克: 0,
        总膳食纤维_克: 0,
        总钠_毫克: 0,
        总钙_毫克: 0,
        总铁_毫克: 0,
        总维生素C_毫克: 0
      }
    }

    const report = generateNutritionReport(event.extra_data.foods_intake, date)
    const stats = dailyStats[date]
    
    stats.餐次数 += 1
    stats.食物种类数 += report.totalFoods
    stats.总重量_克 += report.totalWeight
    stats.总能量_千卡 += report.nutrition.energy_kcal
    stats.总蛋白质_克 += report.nutrition.protein_g
    stats.总脂肪_克 += report.nutrition.fat_g
    stats.总碳水化合物_克 += report.nutrition.carb_g
    stats.总膳食纤维_克 += report.nutrition.fiber_g
    stats.总钠_毫克 += report.nutrition.sodium_mg
    stats.总钙_毫克 += report.nutrition.calcium_mg
    stats.总铁_毫克 += report.nutrition.iron_mg
    stats.总维生素C_毫克 += report.nutrition.vitamin_c_mg
  })

  // 转换为数组并排序
  const dailyStatsArray = Object.values(dailyStats).sort((a: any, b: any) => 
    a.日期.localeCompare(b.日期)
  )

  if (dailyStatsArray.length > 0) {
    const worksheet = XLSX.utils.json_to_sheet(dailyStatsArray)
    
    // 设置列宽
    const colWidths = [
      { wch: 12 }, // 日期
      { wch: 8 },  // 餐次数
      { wch: 10 }, // 食物种类数
      { wch: 10 }, // 总重量
      { wch: 12 }, // 总能量
      { wch: 12 }, // 总蛋白质
      { wch: 10 }, // 总脂肪
      { wch: 14 }, // 总碳水化合物
      { wch: 12 }, // 总膳食纤维
      { wch: 10 }, // 总钠
      { wch: 10 }, // 总钙
      { wch: 10 }, // 总铁
      { wch: 12 }  // 总维生素C
    ]
    worksheet['!cols'] = colWidths

    XLSX.utils.book_append_sheet(workbook, worksheet, '每日营养统计')
  } else {
    const emptyWorksheet = XLSX.utils.json_to_sheet([{ 提示: '没有找到饮食跟踪记录' }])
    XLSX.utils.book_append_sheet(workbook, emptyWorksheet, '无数据')
  }

  // 导出文件
  XLSX.writeFile(workbook, filename)
}

/**
 * 批量导出多个用户的饮食记录
 */
export function exportMultiUserDietRecords(
  userRecords: Array<{ username: string; events: Event[] }>,
  filename = `多用户饮食记录_${new Date().toISOString().split('T')[0]}.xlsx`
) {
  const workbook = XLSX.utils.book_new()

  userRecords.forEach(({ username, events }) => {
    const { detailData } = convertDietEventsToExportData(events)
    
    if (detailData.length > 0) {
      const worksheet = XLSX.utils.json_to_sheet(detailData)
      
      // 设置列宽
      const colWidths = [
        { wch: 12 }, { wch: 8 }, { wch: 20 }, { wch: 10 },
        { wch: 10 }, { wch: 10 }, { wch: 10 }, { wch: 12 },
        { wch: 10 }, { wch: 8 }, { wch: 8 }, { wch: 8 },
        { wch: 10 }, { wch: 20 }
      ]
      worksheet['!cols'] = colWidths

      // 使用用户名作为工作表名称（限制长度）
      const sheetName = username.length > 31 ? username.substring(0, 31) : username
      XLSX.utils.book_append_sheet(workbook, worksheet, sheetName)
    }
  })

  // 如果没有任何数据，创建空工作表
  if (workbook.SheetNames.length === 0) {
    const emptyWorksheet = XLSX.utils.json_to_sheet([{ 提示: '没有找到任何饮食跟踪记录' }])
    XLSX.utils.book_append_sheet(workbook, emptyWorksheet, '无数据')
  }

  XLSX.writeFile(workbook, filename)
}
